package com.neuedu.mblog.dao;

import com.neuedu.mblog.entiry.FlywaySchemaHistory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
* 项目：his
* 创建时间：  2021-12-28 15:47:16
* 作者 :金山老师
* 描述 : 数据访问层
*/
public class FlywaySchemaHistoryDao extends BaseDao<FlywaySchemaHistory> {

    private QueryRunner run = new QueryRunner( dataSource );

    @Override
    public List<FlywaySchemaHistory> selectList(String sql ) throws Exception {
        throw  new Exception("暂未实现的方法");
    }


    /**
     * 批量查询
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    @Override
    public List<FlywaySchemaHistory> selectList(String sql, Object ... params ) throws SQLException {

        //每张表不一样的放，解析结果集
        List<FlywaySchemaHistory> list = run.query(sql, new ResultSetHandler<List<FlywaySchemaHistory>>() {
            @Override
            public List<FlywaySchemaHistory> handle(ResultSet rs) throws SQLException {
                List list = new ArrayList();
                while(rs.next()) {
                    FlywaySchemaHistory flywaySchemaHistory = new FlywaySchemaHistory();

                    flywaySchemaHistory.setInstalledRank(rs.getInt("installed_rank"));
                    flywaySchemaHistory.setVersion(rs.getString("version"));
                    flywaySchemaHistory.setDescription(rs.getString("description"));
                    flywaySchemaHistory.setType(rs.getString("type"));
                    flywaySchemaHistory.setScript(rs.getString("script"));
                    flywaySchemaHistory.setChecksum(rs.getInt("checksum"));
                    flywaySchemaHistory.setInstalledBy(rs.getString("installed_by"));
                    flywaySchemaHistory.setInstalledOn(rs.getTimestamp("installed_on"));
                    flywaySchemaHistory.setExecutionTime(rs.getInt("execution_time"));
                    flywaySchemaHistory.setSuccess(rs.getInt("success"));

                    list.add(flywaySchemaHistory);
                }
                return list;
            }
        },params);

        return list;
    }

    /**
     *
     * @param id 查询主键
     * @return  FlywaySchemaHistory 根据主键查询的数据
     * @throws SQLException
     */
    @Override
    public FlywaySchemaHistory selectById(Integer id) throws SQLException {

        //参数列表
        Object[] params = {id};

        String sql = "select installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success from flyway_schema_history where id = ?";
        FlywaySchemaHistory flywaySchemaHistory = run.query(sql,new ResultSetHandler<FlywaySchemaHistory>() {
            @Override
            public FlywaySchemaHistory handle(ResultSet rs) throws SQLException {
                FlywaySchemaHistory flywaySchemaHistory = null;
                if(rs.next()) {
                    flywaySchemaHistory = new FlywaySchemaHistory();

                        flywaySchemaHistory.setInstalledRank(rs.getInt("installed_rank"));
                        flywaySchemaHistory.setVersion(rs.getString("version"));
                        flywaySchemaHistory.setDescription(rs.getString("description"));
                        flywaySchemaHistory.setType(rs.getString("type"));
                        flywaySchemaHistory.setScript(rs.getString("script"));
                        flywaySchemaHistory.setChecksum(rs.getInt("checksum"));
                        flywaySchemaHistory.setInstalledBy(rs.getString("installed_by"));
                        flywaySchemaHistory.setInstalledOn(rs.getTimestamp("installed_on"));
                        flywaySchemaHistory.setExecutionTime(rs.getInt("execution_time"));
                        flywaySchemaHistory.setSuccess(rs.getInt("success"));
                }
                return  flywaySchemaHistory;
            }
        },params);
        return flywaySchemaHistory;
    }

    /**
    *
    * @param flywaySchemaHistory 待保存的实体对象
    * @return  int 影响行数
    * @throws SQLException
    */
    @Override
    public int save(FlywaySchemaHistory flywaySchemaHistory) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO flyway_schema_history (  ");
        sql.append("  version, description, type, script, checksum, installed_by, installed_on, execution_time, success ");
        sql.append(" )  ");
        sql.append(" VALUES ");
        sql.append("   ( ");
        sql.append("       ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?  ");
        sql.append("   )  ");

        Object[] params = {
            
            flywaySchemaHistory.getVersion(),  
            flywaySchemaHistory.getDescription(),  
            flywaySchemaHistory.getType(),  
            flywaySchemaHistory.getScript(),  
            flywaySchemaHistory.getChecksum(),  
            flywaySchemaHistory.getInstalledBy(),  
            flywaySchemaHistory.getInstalledOn(),  
            flywaySchemaHistory.getExecutionTime(),  
            flywaySchemaHistory.getSuccess()
        };

        return run.update(sql.toString(),params);
    }


    /**
    * 根据主键更新
    * @param  flywaySchemaHistory
    * @return
    * @throws SQLException
    */
    @Override
    public int update(FlywaySchemaHistory flywaySchemaHistory) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE 				");
        sql.append("   flyway_schema_history           ");
        sql.append(" SET                    ");
            
            sql.append("   version  = ?,  ");
            sql.append("   description  = ?,  ");
            sql.append("   type  = ?,  ");
            sql.append("   script  = ?,  ");
            sql.append("   checksum  = ?,  ");
            sql.append("   installed_by  = ?,  ");
            sql.append("   installed_on  = ?,  ");
            sql.append("   execution_time  = ?,  ");
            sql.append("   success  = ?");

        sql.append(" WHERE installed_rank= ?            ");

        Object[] params = {
                
                flywaySchemaHistory.getVersion(), 
                flywaySchemaHistory.getDescription(), 
                flywaySchemaHistory.getType(), 
                flywaySchemaHistory.getScript(), 
                flywaySchemaHistory.getChecksum(), 
                flywaySchemaHistory.getInstalledBy(), 
                flywaySchemaHistory.getInstalledOn(), 
                flywaySchemaHistory.getExecutionTime(), 
                flywaySchemaHistory.getSuccess(), 
            flywaySchemaHistory.getInstalledRank()
        };
        return run.update(sql.toString(),params);

    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from flyway_schema_history where installed_rank = ?";
        Object[] params = {id};
        return run.update(sql,params);
    }

}
